/* Copyright (c) 2001-2011, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

package org.ewhoxford.swt.bloodpressure.db;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import org.hsqldb.Trigger;
import org.hsqldb.lib.StringUtil;

// peterhudson@users 20020130 - patch 478657 by peterhudson - new class
// fredt@users 20030727 - signature altered
// boucherb@users 20040315 - sample updated

/**
 * <P>
 * Sample code for use of triggers in hsqldb.
 * 
 * This class org.hsqldb.sample package, but a typical implementation is in
 * users's class hierarchy.
 * 
 * SQL to invoke is:
 * <p>
 * CREATE TRIGGER triggerSample BEFORE|AFTER INSERT|UPDATE|DELETE ON myTable
 * [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL "myPackage.trigClass"<br>
 * 
 * This will create a thread that will wait for its firing event to occur; when
 * this happens, the trigger's thread runs the 'trigClass.fire' Note that this
 * is still in the same Java Virtual Machine as the database, so make sure the
 * fired method does not hang.
 * <p>
 * 
 * There is a queue of events waiting to be run by each trigger thread. This is
 * particularly useful for 'FOR EACH ROW' triggers, when a large number of
 * trigger events occur in rapid succession, without the trigger thread getting
 * a chance to run. If the queue becomes full, subsequent additions to it cause
 * the database engine to suspend awaiting space in the queue. Take great care
 * to avoid this situation if the trigger action involves accessing the
 * database, as deadlock will occur. This can be avoided either by ensuring the
 * QUEUE parameter makes a large enough queue, or by using the NOWAIT parameter,
 * which causes a new trigger event to overwrite the most recent event in the
 * queue. The default queue size is 1024.
 * <p>
 * 
 * Ensure that "myPackage.trigClass" is present in the classpath which you use
 * to start hsql.
 * <p>
 * 
 * If the method wants to access the database, it must establish a JDBC
 * connection.
 * <p>
 * 
 * When the 'fire' method is called, it is passed the following arguments:
 * <p>
 * 
 * fire (int type, String trigName, String tabName, Object oldRow[], Object[]
 * newRow)
 * <p>
 * 
 * where 'type' is one of the values enumerated in the Trigger interface and the
 * 'oldRow'/'newRow' pair represents the rows acted on. The first length - 1
 * array slots contain column values and the final slot contains either null or
 * the value of the internally assigned row identity, if the concerned table has
 * no primary key. The final slot must _never_ be modified.
 * <p>
 * 
 * The mapping of row classes to database types is specified in
 * /doc/hsqlSyntax.html#Datatypes.
 * <p>
 * 
 * To be done:
 * <p>
 * 
 * <ol>
 * <li>Implement the "jdbc:default:connection: URL to provide transparent and
 * portable access to internal connections for use in triggers and stored
 * procedures.
 * <p>
 * 
 * <li>Implement declaritive column to trigger method argument mapping,
 * conditional execution (WHEN clause), etc.
 * <p>
 * 
 * <li>Investigate and refine synchronous and asynchronous trigger models.
 * <p>
 * 
 * Because certain combinations of trigger create parameters cause the
 * individual triggered actions of a multirow update to run in different
 * threads, it is possible for an 'after' trigger to run before its
 * corresponding 'before' trigger; the acceptability and implications of this
 * needs to be investigated, documented and the behaviour of the engine fully
 * specified.
 * 
 * <li>Investigate and implement the SQL 200n specified execution stack under
 * arbitrary triggered action and SQL-invoked routine call graphs.
 * </ol>
 * 
 * @author Peter Hudson
 * @author boucherb@users
 * @version 1.7.2
 * @since 1.7.0
 */
public class TriggerSample implements Trigger {

	static final PrintWriter out = new PrintWriter(System.out);
	static final String drv = "org.hsqldb.jdbc.JDBCDriver";
	static final String url = "jdbc:hsqldb:mem:trigger-sample";
	static final String usr = "SA";
	static final String pwd = "";
	static final String impl = TriggerSample.class.getName();
	static final String tn = "trig_test";
	static final String drop_test_table_stmt = "DROP TABLE " + tn
			+ " IF EXISTS";
	static final String create_test_table_stmt = "CREATE TABLE " + tn
			+ "(id INTEGER PRIMARY KEY, value VARCHAR(20))";
	static final String drop_audit_table_stmt = "DROP TABLE audit IF EXISTS";
	static final String create_audit_table_stmt = "CREATE TABLE audit("
			+ "id  INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1), "
			+ "op  VARCHAR(6), " + "tn  VARCHAR(20), " + "ors LONGVARCHAR, "
			+ "nrs LONGVARCHAR, " + "ts  TIMESTAMP DEFAULT CURRENT_TIMESTAMP)";
	static final String audit_insert_stmt = "INSERT INTO audit(op, tn, ors, nrs) VALUES(?, ?, ?, ?)";

	/**
	 * A sample HSQLDB Trigger interface implementation.
	 * <p>
	 * 
	 * This sample prints information about the firing trigger and records
	 * actions in an audit table.
	 * <p>
	 * 
	 * The techniques used here are simplified dramatically for demonstration
	 * purposes and are in no way recommended as a model upon which to build
	 * actual installations involving triggered actions.
	 * 
	 * @param typ
	 *            trigger type
	 * @param trn
	 *            trigger name
	 * @param tn
	 *            table name
	 * @param or
	 *            old row
	 * @param nr
	 *            new row
	 */
	public void fire(int typ, String trn, String tn, Object[] or, Object[] nr) {

		synchronized (TriggerSample.class) {
			String ors = or == null ? "null" : StringUtil.arrayToString(or);
			String nrs = nr == null ? "null" : StringUtil.arrayToString(nr);

			out.println("----------------------------------------");
			out.println(getTriggerDescriptor(trn, typ, tn));
			out.println("old row : " + ors);
			out.println("new row : " + nrs);
			out.flush();

			if ("TRIG_TEST".equals(tn)) {
				switch (typ) {

				case INSERT_BEFORE_ROW: {

					// Business rule: ID shall be less than 11.
					// (Marti DiBergi, we love you ;-)
					// You can cast row[i] given your knowledge of what
					// the table format is:
					final int ID = ((Number) nr[0]).intValue();

					doAssert(ID < 11, "ID < 11");

					break;
				}
				case UPDATE_BEFORE_ROW: {

					// Business rule: ignore update of VALUE 'unchangable'.
					if ("unchangable".equals(or[1])) {
						nr[1] = or[1]; // short-circuit the update
					}

					// !!!Warning!!!
					// The engine does not check the class of substituted
					// values; it's up to you to use the correct class.
					// For example, this will cause database curruption:
					// nr[1] = new Integer(5);
					break;
				}
				}
			}

			doAuditStep(typ, tn, ors, nrs);
		}
	}

	private static void doAssert(boolean b, String msg) {

		if (b) {

			// do nothing
		} else {
		//	throw org.hsqldb.error.Error.error(ErrorCode.GENERAL_ERROR, msg);
		}
	}

	private static void doAuditStep(int typ, String tn, String ors, String nrs) {

		Connection conn;
		PreparedStatement stmt;

		switch (typ) {

		case INSERT_AFTER_ROW:
		case UPDATE_AFTER_ROW:
		case DELETE_AFTER_ROW: {
			try {
				conn = getConnection();
				stmt = conn.prepareStatement(audit_insert_stmt);

				stmt.setString(1, getOperationSpec(typ));
				stmt.setString(2, tn);
				stmt.setString(3, ors);
				stmt.setString(4, nrs);
				stmt.executeUpdate();
				conn.close();
			} catch (SQLException se) {
				se.printStackTrace();
			}
		}
		}
	}

	public static String getWhenSpec(int type) {

		switch (type) {

		case INSERT_BEFORE_ROW:
		case UPDATE_BEFORE_ROW:
		case DELETE_BEFORE_ROW: {
			return "BEFORE";
		}
		case INSERT_AFTER:
		case INSERT_AFTER_ROW:
		case UPDATE_AFTER:
		case UPDATE_AFTER_ROW:
		case DELETE_AFTER:
		case DELETE_AFTER_ROW: {
			return "AFTER";
		}
		default: {
			return "";
		}
		}
	}

	public static String getOperationSpec(int type) {

		switch (type) {

		case INSERT_AFTER:
		case INSERT_AFTER_ROW:
		case INSERT_BEFORE_ROW: {
			return "INSERT";
		}
		case UPDATE_AFTER:
		case UPDATE_AFTER_ROW:
		case UPDATE_BEFORE_ROW: {
			return "UPDATE";
		}
		case DELETE_AFTER:
		case DELETE_AFTER_ROW:
		case DELETE_BEFORE_ROW: {
			return "DELETE";
		}
		default: {
			return "";
		}
		}
	}

	public static String getQueueSpec(int qs) {
		return (qs < 0) ? "" : ("QUEUE " + qs);
	}

	public static String getForEachSpec(int type) {

		switch (type) {

		case INSERT_BEFORE_ROW:
		case INSERT_AFTER_ROW:
		case UPDATE_BEFORE_ROW:
		case UPDATE_AFTER_ROW:
		case DELETE_AFTER_ROW:
		case DELETE_BEFORE_ROW: {
			return "FOR EACH ROW";
		}
		default: {
			return "FOR EACH STATEMENT";
		}
		}
	}

	public static String getTriggerDDL(String trn, int typ, String tab, int qs,
			String impl) throws SQLException {

		StringBuffer sb = new StringBuffer();

		sb.append("CREATE TRIGGER ");
		sb.append(trn);
		sb.append(' ');
		sb.append(getWhenSpec(typ));
		sb.append(' ');
		sb.append(getOperationSpec(typ));
		sb.append(" ON ");
		sb.append(tab);
		sb.append(' ');
		sb.append(getForEachSpec(typ));
		sb.append(' ');
		sb.append(getQueueSpec(qs));
		sb.append(" CALL \"");
		sb.append(impl);
		sb.append("\"");

		return sb.toString();
	}

	public static String getTriggerDescriptor(String trn, int typ, String tab) {

		StringBuffer sb = new StringBuffer();

		sb.append("TRIGGER : ");
		sb.append(trn);
		sb.append(' ');
		sb.append(getWhenSpec(typ));
		sb.append(' ');
		sb.append(getOperationSpec(typ));
		sb.append(" ON ");
		sb.append(tab);
		sb.append(' ');
		sb.append(getForEachSpec(typ));

		return sb.toString();
	}

	private static Connection getConnection() throws SQLException {

		try {
			Class.forName(drv).newInstance();

			return DriverManager.getConnection(url, usr, pwd);
		} catch (SQLException se) {
			throw se;
		} catch (Exception e) {
			throw new SQLException(e.toString());
		}
	}

	private static void createTrigger(Statement stmt, String trn, int typ)
			throws SQLException {
		stmt.execute(getTriggerDDL(trn, typ, tn, 0, impl));
	}

	private static void setup() throws SQLException {

		Connection conn = getConnection();
		Statement stmt = conn.createStatement();

		stmt.execute(drop_test_table_stmt);
		stmt.execute(create_test_table_stmt);
		stmt.execute(drop_audit_table_stmt);
		stmt.execute(create_audit_table_stmt);
		createTrigger(stmt, "tibr_" + tn, INSERT_BEFORE_ROW);
		createTrigger(stmt, "tia_" + tn, INSERT_AFTER);
		createTrigger(stmt, "tiar_" + tn, INSERT_AFTER_ROW);
		createTrigger(stmt, "tubr_" + tn, UPDATE_BEFORE_ROW);
		createTrigger(stmt, "tua_" + tn, UPDATE_AFTER);
		createTrigger(stmt, "tuar_" + tn, UPDATE_AFTER_ROW);
		createTrigger(stmt, "tdbr_" + tn, DELETE_BEFORE_ROW);
		createTrigger(stmt, "tda_" + tn, DELETE_AFTER);
		createTrigger(stmt, "tdar_" + tn, DELETE_AFTER_ROW);
		stmt.close();
		conn.close();
	}

	private static void doSomeWork() throws SQLException {

		Connection conn = getConnection();
		Statement stmt = conn.createStatement();

		conn.setAutoCommit(false);
		stmt.execute("INSERT INTO trig_test VALUES (1, 'hello')");
		stmt.execute("INSERT INTO trig_test VALUES (2, 'now what?')");
		stmt.execute("INSERT INTO trig_test VALUES (3, 'unchangable')");
		stmt.execute("INSERT INTO trig_test VALUES (4, 'goodbye')");
		conn.commit();
		dumpTable("trig_test");
		stmt.execute("UPDATE trig_test SET value = 'all done'");
		conn.commit();
		dumpTable("trig_test");
		stmt.execute("DELETE FROM trig_test");
		conn.rollback();
		dumpTable("trig_test");

		try {
			stmt.execute("INSERT INTO trig_test VALUES(11, 'whatever')");
		} catch (SQLException se) {
			se.printStackTrace();
		}

		stmt.execute("INSERT INTO trig_test VALUES(10, 'whatever')");
		conn.commit();
		dumpTable("trig_test");
		stmt.close();
		conn.close();
	}

	private static void dumpTable(String tn) throws SQLException {

		Connection conn = getConnection();
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("select * from " + tn);
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();

		out.println();
		out.println("****************************************");
		out.println("DUMP FOR TABLE: " + tn);
		out.println("****************************************");
		out.flush();

		while (rs.next()) {
			out.print("[");

			for (int i = 1; i <= count; i++) {
				out.print(rs.getString(i));

				if (i < count) {
					out.print(" : ");
				}
			}

			out.println("]");
		}

		out.println();
		out.flush();
		rs.close();
		stmt.close();
		conn.close();
	}

	private static void runSample() throws SQLException {

		setup();
		doSomeWork();
		dumpTable("audit");
	}

	public static void main(String[] args) throws SQLException {
		runSample();
	}
}
/*
 * test SQL CREATE CACHED TABLE trig_test (int_field integer) CREATE TRIGGER
 * ins_before BEFORE INSERT ON trig_test CALL "org.hsqldb.sample.TriggerSample"
 * CREATE TRIGGER ins_after AFTER INSERT ON trig_test CALL
 * "org.hsqldb.sample.TriggerSample" CREATE TRIGGER upd_before BEFORE UPDATE ON
 * trig_test CALL "org.hsqldb.sample.TriggerSample" CREATE TRIGGER upd_after
 * AFTER UPDATE ON trig_test CALL "org.hsqldb.sample.TriggerSample" CREATE
 * TRIGGER upd_before_row BEFORE UPDATE ON trig_test FOR EACH ROW CALL
 * "org.hsqldb.sample.TriggerSample" CREATE TRIGGER upd_after_row AFTER UPDATE
 * ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample" CREATE
 * TRIGGER del_before BEFORE DELETE ON trig_test CALL
 * "org.hsqldb.sample.TriggerSample" CREATE TRIGGER del_after AFTER DELETE ON
 * trig_test CALL "org.hsqldb.sample.TriggerSample" CREATE TRIGGER
 * del_before_row BEFORE DELETE ON trig_test FOR EACH ROW CALL
 * "org.hsqldb.sample.TriggerSample" CREATE TRIGGER del_after_row AFTER DELETE
 * ON trig_test FOR EACH ROW CALL "org.hsqldb.sample.TriggerSample" INSERT INTO
 * trig_test VALUES (1) INSERT INTO trig_test VALUES (2) INSERT INTO trig_test
 * VALUES (3) UPDATE trig_test SET int_field = int_field + 3 DELETE FROM
 * trig_test
 */
